do "E:\yungyu\lottery_baby\do\set_environment.do"

capture log close
log using "$log\2.6.winners_children_`c(current_date)'.log" , replace
clear
set more off

foreach x in fr mo{

	use "$wdata\project_lottery_fertility_full_panel_93109.dta", clear

	keep if norm == -1
	keep houseid pers_idn spouse_idn real_win_year win_year year win_age winyr_pers_real_prize_aftertax
	duplicates drop

	rename pers_idn `x'_idn
	joinby `x'_idn using "$public\observation\real_jhq_v2.dta"
	keep if born_year <= year //Children before lottery
	keep if death_year > year //Alive before lottery
	gen age = year - born_year
	gen born_month = month(born_date)
	
	rename `x'_idn pers_idn
	
	foreach y in idn gender born_year born_month born_place death_year age{
		rename `y' child_`y'
	}
	
	keep houseid pers_idn spouse_idn real_win_year win_year year win_age winyr_pers_real_prize_aftertax child*
	
	save "$wdata/winners_children_`x'.dta", replace
}

clear
foreach x in fr mo{
	ap using "$wdata/winners_children_`x'.dta"
}
duplicates drop

sort houseid real_win_year child_born_year
by houseid real_win_year: gen child_birth_order = _n 
order child_birth_order, a(child_born_place)

** Panel from 93 to 109
expand 17
sort houseid child_idn
by houseid child_idn: replace year = _n + 92
gen norm_year = year - win_year

gen died = child_death_year <= year

** 戶籍地縣市鄉鎮區 (93-96年來自ibet，97年以後來自jhq)
forv i = 93(1)109{
	dis "merge personal resident information of year `i'..."
	merge m:1 year pers_idn using "$public/residence/resident_`i'.dta", update noreport keepusing(hsn_cd town_cd)
	quiet drop if _m == 2
	drop _m
}
rename hsn_cd pers_hsn
rename town_cd pers_town

rename pers_idn winner_idn
rename child_idn pers_idn
forv i = 93(1)109{
	dis "merge child's resident information of year `i'..."
	merge m:1 year pers_idn using "$public/residence/resident_`i'.dta", update noreport keepusing(hsn_cd town_cd)
	quiet drop if _m == 2
	drop _m
}
rename hsn_cd child_hsn
rename town_cd child_town
rename pers_idn child_idn 
rename winner_idn pers_idn 

gen livetogether = pers_hsn == child_hsn & pers_town == child_town & pers_hsn != "" & pers_town != ""

** 戶籍地縣市鄉鎮區村里 (僅97年以後來自jhq)
rename child_idn idn
forv i = 97(1)109{
	dis "merge child's resident information of year `i'..."
	merge m:1 year idn using "E:/yungyu/boundary_disc/wdata/jhq/jhq_cleaned_`i'.dta", update noreport keepusing(res_hsn_cd res_town_cd res_vill_cd)
	quiet drop if _m == 2
	drop _m
}
replace child_hsn = res_hsn_cd if res_hsn_cd != ""
replace child_town = res_town_cd if res_town_cd != ""
rename res_vill_cd child_vill
rename idn child_idn 
drop res_hsn_cd res_town_cd

rename child_hsn res_hsn_cd
rename child_town res_town_cd
rename child_vill res_vill_cd

merge m:1 res_hsn_cd res_town_cd res_vill_cd using "$wdata/top1_prob_village.dta"
drop if _m == 2
drop _m
recode top1 . = 0
recode top5 . = 0
rename top1 prob_top1_vill
rename top5 prob_top5_vill

merge m:1 res_hsn_cd res_town_cd using "$wdata/top1_prob_township.dta"
drop if _m == 2
drop _m
recode top1 . = 0
recode top5 . = 0
rename top1 prob_top1_town
rename top5 prob_top5_town 

rename res_hsn_cd child_hsn
rename res_town_cd child_town
rename res_vill_cd child_vill
foreach y in prob_top1_vill prob_top5_vill prob_top1_town prob_top5_town{
	rename `y' res_`y'
}

** 父母房屋所在地
foreach x in pers spouse{
	rename `x'_idn idn
	forv i = 93(1)109{
		dis "merge parent's housing information of year `i'..."
		merge m:1 year idn using "$wdata/hou_max_prop_`i'.dta", update noreport
		quiet drop if _m == 2
		drop _m
	}
	foreach y in prob_top1_vill prob_top5_vill prob_top1_town prob_top5_town{
		rename `y' `x'_`y'
	}
	rename idn `x'_idn 
}

foreach y in prob_top1_vill prob_top5_vill prob_top1_town prob_top5_town{
	gen parhou_`y' = pers_`y'
	replace parhou_`y' = spouse_`y' if spouse_`y' != . & spouse_`y' > pers_`y'
}

** Income
rename child_idn idn
forv i =93(1)109{
	dis "merge `x' income information of year `i'..."
	cap noi merge m:1 year idn using "$wdata/income_`i'.dta" , update noreport //As couple idn with some missing, cannot uniquely identify, using m:1 to merge
	cap drop if _m==2
	cap drop _m income_*
	** Earnings
	dis "merge `x' earnings information of year `i'..."
	merge m:1 year idn using "$earnings/earnings_`i'.dta" , update noreport  //As couple idn with some missing, cannot uniquely identify, using m:1 to merge
	quiet drop if _m==2
	if `i' < 104{
		quiet replace wage = wage + est_pay if year == `i'
		drop est
	}
	drop _m
	lab var busi "business income"
	lab var prof "professional income"
	lab var wage "wage income"
	lab var earnings "earnings"
}

** Wealth
rename pers_idn winner_idn
rename idn pers_idn
forv i =93(1)109{
	dis "merge `x' wealth information of year `i'..."
	merge m:1 year pers_idn using "$wealth/ind_wealth_`i'.dta", update replace noreport keepusing(tot_mrkt_net_t21 rl_est_mrkt houseloan_v4 captl_tot_saving value_stk_2 bu_ast_1)
	quiet drop if _m==2
	drop _m
}

quiet{
	gen asset = tot_mrkt_net_t21	
	gen rlest = rl_est
	gen hloan = houseloan_v4
	gen saving = captl_tot_saving
	gen stock = value_stk_2 + bu_ast_1
	replace bu_ast_1 = 0 if bu_ast_1 < 0
	gen grossstock = value_stk_2 + bu_ast_1
	gen grossasset = rl_est_mrkt + captl_tot_saving + value_stk_2 + bu_ast_1
	gen liquidasset = grossstock + saving
}
drop tot_mrkt_net* rl_est_mrkt houseloan_v4 captl_tot_saving value_stk_2 bu_ast_1
	 
foreach y in "income" "busi" "prof" "wage" "earnings" "asset" "rlest" "hloan" "saving" "stock" "grossstock" "grossasset" "liquidasset"{
	dis "set `y' missing to zero"
	recode `y' . = 0
}
rename pers_idn child_idn
rename winner_idn pers_idn 

** 競賽獎金
rename child_idn idn 
merge m:1 year idn using "E:/yungyu/boundary_disc/wdata/imh/award_collapse.dta"
drop if _m == 2
drop _m
rename idn child_idn  

rename child_idn idn 
merge m:1 year idn using "E:/yungyu/boundary_disc/wdata/imh/award_collapse_nonbusi.dta"
drop if _m == 2
drop _m
rename idn child_idn  

** Adjusted for CPI
cap drop _merge
merge m:1 year using "$wdata/cpi.dta", noreport
drop if _m == 2
drop cpi_sy _merge
foreach y in "income" "busi" "prof" "wage" "earnings" "award_amt" "nb_award_amt"{
	dis "adjust `y' by cpi..."
	replace `y' = `y'/cpi*100
}

gen employed = earning > 0

** Education Record
dis "merge IBRT education information"
rename child_idn student_idn
merge m:1 student_idn year using "$wdata/ibrt413_enrollment_record.dta", update replace noreport
quiet drop if _m==2
drop _m

rename student_idn idn 
forv i =93(1)109{
	dis "merge IBET education information of year `i'..."
	merge m:1 year idn using "$wdata/education_`i'.dta", update replace noreport
	quiet drop if _m==2
	drop _m
}

rename idn child_idn

foreach x of varlist any-oversea{
	recode `x' . = 0
}

rename any college_ibr
sort houseid child_idn year

replace oversea = 0 if year < 98
foreach x in college_ibr college_ibe oversea any2CV public2CV private2CV unknown2CV any4C public4C private4C unknown4C any4CV public4CV private4CV unknown4CV anyD publicD privateD unknownD anyJC publicJC privateJC unknownJC anyM publicM privateM unknownM top5 top10 top20{
	gen any`x' = `x'
	by houseid child_idn: replace any`x' = any`x'[_n-1] if _n > 1 & any`x'[_n-1] == 1
}

foreach x in public private{
	gen anyany`x' = 0
	foreach y in 2CV 4C 4CV D JC M{
		replace anyany`x' = 1 if any`x'`y' == 1
	}
}

** IBET Record
rename child_idn idn
forv i =93(1)109{
	dis "merge IBET information of year `i'..."
	merge m:1 year idn using "$wdata/ibet700child_`i'.dta", update replace noreport
	quiet drop if _m==2
	drop _m
}
rename idn child_idn
recode anyibet . = 0
recode anyibetch . = 0

* Med college
rename child_idn student_idn
merge m:1 year student_idn using "$wdata/medcollege.dta"
drop if _m == 2
drop _m

rename student_idn child_idn
recode medicine . = 0
recode dentistry . = 0
recode medcollege . = 0

sort houseid child_idn year

foreach x in medicine dentistry medcollege{
	gen any`x' = `x'
	by houseid child_idn: replace any`x' = any`x'[_n-1] if _n > 1 & any`x'[_n-1] == 1
}

* First Major
rename child_idn student_idn
merge m:1 student_idn using "$wdata/first_major.dta"
drop if _m == 2
drop _m

save "$wdata\project_lottery_child_quality_full_panel_93109.dta", replace

log close
